CREATE PROCEDURE [acms].[PostPosition_BuildTree]
	@SiteId int,
	@ParentGuid uniqueidentifier, 
	@PublishDate DateTime
AS
	
	if @ParentGuid is null begin
		delete acms.PostPositions
		from acms.PostPositions a inner join acms.Post_View b on a.PostId = b.Id
		where b.SiteId = @SiteId and b.ParentGuid is null and isNull(a.PublishDate, '9999/12/31') = isNull(@PublishDate, '9999/12/31')
		
		insert into acms.PostPositions
			(PostId, PublishDate, ParentPath, ParentSequence, Sequence)
		select Id, @PublishDate, null, null, 0
		from acms.Post_View
		where SiteId = @SiteId 
			and ParentGuid is null 
			and 
				((@PublishDate is null and PublishFrom is null and PublishTo is null)
				or
				(@PublishDate is not null and PublishFrom <= @PublishDate and isNull(PublishTo, '9999/12/31') > @PublishDate))
	end
	else begin
		declare @ParentPath varchar(255), @ParentSequence varchar(20)

		select @ParentPath = IsNull(ParentPath, '') + Name + '/', @ParentSequence = IsNull(ParentSequence, '') + a.Sequence
		from acms.PostPositions a inner join acms.Post_View b on a.PostId = b.Id
		where isNull(a.PublishDate, '9999/12/31') = isNull(@PublishDate, '9999/12/31') and b.PostGuid = @ParentGuid

		delete acms.PostPositions
		from acms.PostPositions a inner join acms.Post_View b on a.PostId = b.Id
		where b.SiteId = @SiteId and b.ParentGuid = @ParentGuid and isNull(a.PublishDate, '9999/12/31') = isNull(@PublishDate, '9999/12/31')

		insert into acms.PostPositions
			(PostId, PublishDate, ParentPath, ParentSequence, Sequence)
		select Id, @PublishDate, @ParentPath, @ParentSequence, Sequence
		from acms.Post_View
		where SiteId = @SiteId 
			and ParentGuid = @ParentGuid 
			and 
				((@PublishDate is null and PublishFrom is null and PublishTo is null)
				or
				(@PublishDate is not null and PublishFrom <= @PublishDate and isNull(PublishTo, '9999/12/31') > @PublishDate))
	end
	
	--do for children
	declare @PostGuid uniqueidentifier
	
	declare cur cursor local for
	
	select a.PostGuid
	From acms.Post_View a
		inner join acms.PostPositions b on a.Id = b.PostId and isNull(b.PublishDate, '9999/12/31') = isnull(@PublishDate, '9999/12/31')
	where
		SiteId = @SiteId and 
		((@ParentGuid is not null and a.ParentGuid = @ParentGuid )
		or 
		(@ParentGuid is null and a.ParentGuid is null))

	open cur
	
	fetch next from cur into @PostGuid
	while @@fetch_status = 0 begin
		exec [acms].[PostPosition_BuildTree] @SiteId, @PostGuid, @PublishDate
		
		fetch next from cur into @PostGuid
	end
	
	close cur
	
	deallocate cur
RETURN 0;